﻿using SimpleAccess.SQLite;
using System;
using System.Data.SQLite;
using System.IO;
using System.Threading;

namespace FaceIdentifier
{
    class FaceDB
    {
        private static readonly log4net.ILog Log = log4net.LogManager.GetLogger(typeof(FaceDB));
        private static string dbPath = "FaceDB.db";
        private SQLiteSimpleAccess simpleAccess;
        private static FaceDB faceDB;
        private static ReaderWriterLockSlim _rwlock = new ReaderWriterLockSlim();

        private FaceDB()
        {
            simpleAccess = new SQLiteSimpleAccess("Data Source="+ dbPath);
        }

        public static FaceDB getInstance()
        {
            lock (_rwlock)
            {
                if (!File.Exists("FaceDB.db"))
                {
                    SQLiteConnection.CreateFile(dbPath);
                    createTable();
                }
                if (faceDB == null)
                {
                    faceDB = new FaceDB();
                }
                return faceDB;
            }
        }

        private static void createTable()
        {
            try
            {
                SQLiteConnection sqliteConn = new SQLiteConnection("data source=" + dbPath);
                if (sqliteConn.State != System.Data.ConnectionState.Open)
                {
                    sqliteConn.Open();
                    SQLiteCommand cmd = new SQLiteCommand();
                    cmd.Connection = sqliteConn;
                    cmd.CommandText = "CREATE TABLE PrivilegeInfo (personSN STRING PRIMARY KEY, personName STRING, validTo STRING);";
                    cmd.ExecuteNonQuery();
                }
                sqliteConn.Close();
            }
            catch (Exception ex)
            {
                Log.Error("创建本地数据库失败", ex);
            }
        }

        public void AddPrivilegeInfo(PrivilegeInfo info)
        {
            _rwlock.EnterWriteLock();
            try
            {
                DelPrivilegeInfo(info.personSN);
                simpleAccess.ExecuteNonQuery("INSERT INTO dbo.PrivilegeInfo values (@personSN,@personName,@validTo);", info);
            }
            catch (Exception ex)
            {
                Log.Error("添加权限失败", ex);
            }
            finally
            {
                _rwlock.ExitWriteLock();
            }
        }

        public void DelPrivilegeInfo(string personSN)
        {
            _rwlock.EnterWriteLock();
            try
            {
                simpleAccess.ExecuteNonQuery("DELETE FROM dbo.PrivilegeInfo WHERE personSN=@personSN);", new { personSN = personSN });
            }
            catch (Exception ex)
            {
                Log.Error("删除权限失败", ex);
            }
            finally
            {
                _rwlock.ExitWriteLock();
            }
        }

        public PrivilegeInfo GetPrivilegeInfo(string personSN)
        {
            if (!_rwlock.TryEnterReadLock(500))
            {
                return null;
            }
            try
            {
                return simpleAccess.ExecuteEntity<PrivilegeInfo>("SELECT * FROM dbo.PrivilegeInfo WHERE personSN = @personSN;", new { personSN = personSN });
            }
            catch (Exception ex)
            {
                Log.Error("查询权限失败", ex);
                return null;
            }
            finally
            {
                _rwlock.ExitReadLock();
            }

        }
    }
}
